package com.yy.school.util;

import com.yy.school.pojo.Course;
import com.yy.school.pojo.Notice;
import com.yy.school.pojo.Student;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class StudentDBUtil extends DBUtil {
    Scanner sc = new Scanner(System.in);

    public Student getStuBySid(Integer sid) {
        Student stu = null;
        try {
            getConn();
            String sql = "select sid,sname,sex,address from student where sid=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, sid);
            rs = ps.executeQuery();
            if (rs.next()) {
                stu = new Student(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getString(3),
                        rs.getString(4)
                );
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return stu;
    }

    //请假方法
    public int leave() {
        int i = 0;
        System.out.println("请输入请假标题");
        String title = sc.next();
        System.out.println("请输入请加内容");
        String content = sc.next();
        System.out.println("请输入你的id");
        int sid = sc.nextInt();
        System.out.println("请输入老师的id");
        int tid = sc.nextInt();
        System.out.println("请输入开始时间 (yyyy-MM-dd)");
        String beginDate = sc.next();
        System.out.println("请输入结束时间 (yyyy-MM-dd)");
        String endDate = sc.next();
        try {
            getConn();
            String sql = "insert into `leave` (title,content,sid,tid,begindate,enddate) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, title);
            ps.setObject(2, content);
            ps.setObject(3, sid);
            ps.setObject(4, tid);
            ps.setObject(5, beginDate);
            ps.setObject(6, endDate);
            i = ps.executeUpdate();
            if (1 > 0) {
                System.out.println("请假输入成功");
            } else {
                System.out.println("请假审批失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    //故障保修
    public int addFault() {
        int i = 0;
        System.out.println("请输入故障标题");
        String title = sc.next();
        System.out.println("请输入故障内容");
        String content = sc.next();
        System.out.println("请输入你的姓名");
        String name = sc.next();
        System.out.println("请输入你的电话");
        Long tel = sc.nextLong();
        Date fDate = new Date();
        try {
            getConn();
            String sql = "insert into `fault` (title,content,name,tel,fdate) values (?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, title);
            ps.setObject(2, content);
            ps.setObject(3, name);
            ps.setObject(4, tel);
            ps.setObject(5, fDate);
            i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("故障报修成功");
            } else {
                System.out.println("故障报修失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    //学生查看个人选修课程
    public List<Course> getStuCourse() {
        System.out.println("请输入学生id");
        int sid = sc.nextInt();
        List<Course> list = new ArrayList<>();
        try {
            getConn();
            String sql="select c.cid,c.cname,c.score from course c,student s,choose ch where s.sid=ch.sid and ch.cid=c.cid and s.sid=?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1, sid);
            rs=ps.executeQuery();
            while (rs.next()){
                Course course = new Course(
                        rs.getInt(1),
                        rs.getString(2),
                        rs.getInt(3));
                list.add(course);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return list;
    }
    //查看学校/教师通知
    public List<Notice> getNotice(){
    List<Notice> list=new ArrayList<>();
    try {
        getConn();
        String sql="select nid ,content ,sender,status from notice";
        ps=conn.prepareStatement(sql);
        rs=ps.executeQuery();
        while(rs.next()){

            list.add(new Notice(
                    rs.getInt(1),
                    rs.getString(2),
                    rs.getString(3),
                    rs.getString(4)
            ));
        }
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        closeConn();
    }
    return list;
    }
    //选修课程
    public int addCourse(){
        int i=0;
        System.out.println("请输入学生你的sid");
        Integer sid = sc.nextInt();
        System.out.println("请输入课程号cid");
        Integer cid=sc.nextInt();
        System.out.println("请输入教师id");
        Integer tid=sc.nextInt();
        try {
            getConn();
            String sql="insert into `choose` (sid,cid,tid) values(?,?,?)";
            ps=conn.prepareStatement(sql);
            ps.setObject(1, sid);
            ps.setObject(2, cid);
            ps.setObject(3, tid);
            i=ps.executeUpdate();
            if(i>0){
                System.out.println("选课成功");
            }else{
                System.out.println("选课失败，自行处理");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            closeConn();
        }
        return i;
    }
}
